package com.example.wenhua.mapper;

import com.example.wenhua.entity.Sc;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Map;
import java.math.BigDecimal;

/**
 * 选课数据访问层
 */
@Mapper
public interface ScMapper {
    
    /**
     * 查询所有选课记录（包含学生和课程详细信息）
     */
    @Select("SELECT sc.sno, s.sname, sc.cno, c.cname, c.teacher, sc.grade " +
            "FROM sc " +
            "LEFT JOIN s ON sc.sno = s.sno " +
            "LEFT JOIN c ON sc.cno = c.cno")
    List<Map<String, Object>> findAll();
    
    // 使用XML映射文件中的查询
    List<Map<String, Object>> findWithFilter(@Param("sno") String sno, @Param("cno") String cno, @Param("gradeStatus") String gradeStatus);
    
    /**
     * 根据学号查询选课记录
     */
    @Select("SELECT * FROM sc WHERE sno = #{sno}")
    List<Sc> findBySno(String sno);
    
    /**
     * 根据课程号查询选课记录
     */
    @Select("SELECT * FROM sc WHERE cno = #{cno}")
    List<Sc> findByCno(String cno);
    
    /**
     * 根据学号和课程号查询选课记录
     */
    @Select("SELECT * FROM sc WHERE sno = #{sno} AND cno = #{cno}")
    Sc findBySnoAndCno(@Param("sno") String sno, @Param("cno") String cno);
    
    /**
     * 新增选课记录
     */
    @Insert("INSERT INTO sc(sno, cno, grade) VALUES(#{sno}, #{cno}, #{grade})")
    int insert(Sc sc);
    
    /**
     * 更新成绩
     */
    @Update("UPDATE sc SET grade = #{grade} WHERE sno = #{sno} AND cno = #{cno}")
    int updateGrade(@Param("sno") String sno, @Param("cno") String cno, @Param("grade") BigDecimal grade);
    
    /**
     * 删除选课记录
     */
    @Delete("DELETE FROM sc WHERE sno = #{sno} AND cno = #{cno}")
    int delete(@Param("sno") String sno, @Param("cno") String cno);
    
    /**
     * 查询学生各科成绩（包含课程信息）
     */
    @Select("SELECT sc.sno, sc.cno, c.cname, sc.grade, CASE WHEN sc.grade >= 60 THEN '通过' ELSE '未通过' END as status " +
            "FROM sc JOIN c ON sc.cno = c.cno WHERE sc.sno = #{sno}")
    List<Map<String, Object>> getStudentGradesBySno(String sno);
    
    /**
     * 查询课程不及格学生名单
     */
    @Select("SELECT s.sno, s.sname, sc.grade FROM sc JOIN s ON sc.sno = s.sno " +
            "WHERE sc.cno = #{cno} AND sc.grade < 60")
    List<Map<String, Object>> getFailStudentsByCno(String cno);
    
    /**
     * 按班级统计不及格人数
     */
    @Select("SELECT s.dept, COUNT(*) as failCount, " +
            "(SELECT COUNT(*) FROM s WHERE dept = s.dept) as totalCount " +
            "FROM sc JOIN s ON sc.sno = s.sno " +
            "WHERE sc.grade < 60 " +
            "GROUP BY s.dept")
    List<Map<String, Object>> getFailStatsByDept();
    
    /**
     * 按班级统计各分数段人数
     */
    @Select("SELECT s.dept, " +
            "SUM(CASE WHEN sc.grade >= 0 AND sc.grade < 60 THEN 1 ELSE 0 END) as range0_59, " +
            "SUM(CASE WHEN sc.grade >= 60 AND sc.grade < 70 THEN 1 ELSE 0 END) as range60_69, " +
            "SUM(CASE WHEN sc.grade >= 70 AND sc.grade < 80 THEN 1 ELSE 0 END) as range70_79, " +
            "SUM(CASE WHEN sc.grade >= 80 AND sc.grade < 90 THEN 1 ELSE 0 END) as range80_89, " +
            "SUM(CASE WHEN sc.grade >= 90 AND sc.grade <= 100 THEN 1 ELSE 0 END) as range90_100 " +
            "FROM sc JOIN s ON sc.sno = s.sno " +
            "WHERE sc.grade IS NOT NULL " +
            "GROUP BY s.dept")
    List<Map<String, Object>> getGradeRangeStatsByDept();
}